Quick Search

We have a system that calculates an “Engagement Score” for each person based on their involvement and contributions. Here’s how it works in simple terms:

1. Membership Status: • Member: If you are an active member, you get 4 points.
2. Special Interest Group: • Subscriber: If you subscribe to a special interest group, you get 2 points.
3. PAC Contributions: • Contributor: If you have made contributions to the Political Action Committee, you get a small bonus of 0.05 points.
4. Event Participation: • National Conference: Attending the National Conference gives you 1.5 points. • Fall Conference: Attending the Fall Conference gives you 1.5 points. • Health Policy Conference: Attending the Health Policy Conference gives you 1.5 points. • National Conference On-Demand: Attending the National Conference On-Demand gives you 1.5 points. • Fall Conference On-Demand: Attending the Fall Conference On-Demand gives you 1.5 points.
5. Fellow Status: • Fellow: If you are recognized as a fellow, you get 4 points.

Total Score

Each person’s engagement score is the sum of all these points. The more active and involved you are in different areas, the higher your score will be. This helps us understand and recognize the most engaged and supportive members of our community.

Prioritization

We use this score to rank individuals in our Quick Search application. Those with higher scores are more engaged, and we prioritize them higher in the search results.

• https://quicksearch.aanp.org
• Engagement Scoring Service

Related SPROC

CREATE PROCEDURE [dbo].[sp_aanpapi_GetPersons]
@searchValue varchar(100)
AS

BEGIN

--DECLARE @aptifyResults TABLE (ID int, nameWCompany varchar(100), firstName varchar(100), lastName varchar(100), 
--	title varchar(100), email1 varchar(100), company varchar(100), photo int, searchRank int, entityRecordRank int, topicCodeList varchar(10),
--	memberType varchar(100), line1 varchar(200), line2 varchar(200), city varchar(100), stateProvince varchar(5), postalCode varchar(15), country varchar(50))

--DECLARE @aptifyResults TABLE (ID int, firstName varchar(100), lastName varchar(100), title varchar(100), email1 varchar(100), company varchar(100), photo int, 
--	entityRecordRank int, topicCodeList varchar(10))

DECLARE @aptifyResults TABLE (ID int, firstName varchar(100), lastName varchar(100), email1 varchar(100))

DECLARE @MatchingRecordCount_out AS INT
DECLARE @nextConferenceId_National AS INT, @nextConferenceId_Fall int, @nextConferenceId_HP int,@nextConferenceId_NationalOnDemand AS INT, @nextConferenceId_FallOnDemand int
SET @nextConferenceId_National = COALESCE((SELECT TOP 1 EventID FROM [Event] WHERE IsCancelled = 0 AND EventTitle LIKE '%National%' AND ConferenceType = 'National' AND EndDate >= getdate() ORDER BY StartDate), 0)
SET @nextConferenceId_Fall = COALESCE((SELECT TOP 1 (EventID) FROM [Event] WHERE IsCancelled = 0 AND ConferenceType = 'Fall' AND (EventTitle LIKE '%Fall%' OR EventTitle LIKE '%Specialty%') AND EndDate >= getdate() ORDER BY StartDate), 0)
SET @nextConferenceId_NationalOnDemand = COALESCE((SELECT TOP 1 EventID FROM [Event] WHERE IsCancelled = 0 AND EventTitle LIKE '%National%' AND ConferenceType = 'Virtual' AND EndDate >= getdate() ORDER BY StartDate), 0)
SET @nextConferenceId_FallOnDemand = COALESCE((SELECT TOP 1 (EventID) FROM [Event] WHERE IsCancelled = 0 AND ConferenceType = 'Virtual' AND (EventTitle LIKE '%Fall%' OR EventTitle LIKE '%Specialty%') AND EndDate >= getdate() ORDER BY StartDate), 0)
SET @nextConferenceId_HP = COALESCE((SELECT TOP 1 (EventID) FROM [Event] WHERE IsCancelled = 0 AND EventTitle LIKE '%Health Policy%' AND EndDate >= getdate() ORDER BY StartDate), 0)

INSERT INTO @aptifyResults EXEC Aptify.dbo.spSearchConfigurationShell_Custom_Persons__AANP
	   @SearchConfigurationID = 1, 
       @Entity = 'Persons', 
       @SearchTerm = @searchValue, 
       @MaxRecordsToReturn = 100, 
       @MatchingRecordCount = @MatchingRecordCount_out OUTPUT, 
       @HiddenFilter = '';

WITH tbl_results AS (
	SELECT ap.ID, ap.FirstName, ap.LastName, p.IsMember, p.Line1 AS AddressLine1, p.Line2, p.City, p.StateProvince, p.PostalCode,
		p.Email, p.MemberType,
		(SELECT Aptify.dbo.fnIsSPGMember__c(p.ID)) AS IsSPGSubscriber,
		CASE WHEN p.FellowsID > 1 THEN 1 ELSE 0 END AS IsFellow, 
		CASE WHEN EXISTS(SELECT TOP 1 ContactId FROM ReportMemberDemographics rmd WHERE rmd.ContactId = p.ID AND rmd.LastPacDonation <> '') THEN 1 ELSE 0 END AS DonatesToPAC,
		CASE WHEN EXISTS(SELECT TOP 1 EventAttendeeID FROM EventAttendee ea WHERE ea.FKContactID = p.ID AND ea.FKEventID = @nextConferenceId_National AND ea.CancellationDate IS NULL) THEN 1 ELSE 0 END AS IsAttendingNational,
		CASE WHEN EXISTS(SELECT TOP 1 EventAttendeeID FROM EventAttendee ea WHERE ea.FKContactID = p.ID AND ea.FKEventID = @nextConferenceId_Fall AND ea.CancellationDate IS NULL) THEN 1 ELSE 0 END AS IsAttendingFall,
		CASE WHEN EXISTS(SELECT TOP 1 EventAttendeeID FROM EventAttendee ea WHERE ea.FKContactID = p.ID AND ea.FKEventID = @nextConferenceId_NationalOnDemand AND ea.CancellationDate IS NULL) THEN 1 ELSE 0 END AS IsAttendingHP,
--		CASE WHEN EXISTS(SELECT TOP 1 EventAttendeeID FROM EventAttendee ea WHERE ea.FKContactID = p.ID AND ea.FKEventID = @nextConferenceId_HP AND ea.CancellationDate IS NULL) THEN 1 ELSE 0 END AS IsAttendingHP,
		CASE WHEN EXISTS(SELECT TOP 1 EventAttendeeID FROM EventAttendee ea WHERE ea.FKContactID = p.ID AND ea.FKEventID = @nextConferenceId_FallOnDemand AND ea.CancellationDate IS NULL) THEN 1 ELSE 0 END AS IsAttendingFallOnDemand,
		CASE WHEN EXISTS(SELECT TOP 1 EventAttendeeID FROM EventAttendee ea WHERE ea.FKContactID = p.ID AND ea.FKEventID = @nextConferenceId_NationalOnDemand AND ea.CancellationDate IS NULL) THEN 1 ELSE 0 END AS IsAttendingNationalOnDemand,
		(SELECT PreferredPhone FROM Aptify.dbo.vwPersons pe WHERE pe.ID = p.ID) AS PhoneType,
		COALESCE((SELECT FormattedPhone FROM Aptify.dbo.vwPersons pe WHERE pe.ID = p.ID AND LEN(FormattedPhone) > 2), '') AS PhoneNumber
	FROM Aptify.dbo.vwPersonsWithBestAddress p INNER JOIN @aptifyResults ap ON p.ID = ap.ID
)
SELECT *, ((IsMember * 4) + (IsSPGSubscriber * 2) + (DonatesToPAC * .05) + (IsAttendingFall * 1.5) + (IsAttendingHP * 1.5) + (IsAttendingNational * 1.5) + (IsAttendingNationalOnDemand * 1.5) + (IsAttendingFallOnDemand * 1.5) + (IsFellow * 4)) AS TotalScore
FROM tbl_results
ORDER BY TotalScore DESC, LastName, FirstName, ID DESC

--SELECT TOP 20 p.ID, p.FirstName, p.LastName, p.MiddleName, p.Email, p.MemberType, p.City, p.State
--FROM Aptify.dbo.vwPersons p Where LastName LIKE '%' + @searchValue + '%' OR Email LIKE CONCAT('%', @searchValue, '%')
--ORDER BY LastName, FirstName, ID

END